package cn.itcast.work.dao.category;


import cn.itcast.work.pojo.Article;
import cn.itcast.work.pojo.ArticleCategory;
import cn.itcast.work.pojo.Text;
import cn.itcast.work.pojo.User;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * * @author: Mao Wanting
 * * @date: 2024/5/30
 * *description:查询数据库接口方法，注解实现，数据持久层，使用注解进行开发时，在没有任何处理情况下，列名需要和类的数据成员保持一致
 **/

@Mapper
public interface ArticleSearch {

    //判断是否已经收藏该文章
    @Select("select count(*) " +
            "from article " +
            "where user_id=#{userid} " +
            "and article_id=#{textid}")
    public Integer selectCollection(int userid, int textid);

    /*
     * 增加
     */

    //    a.增加一个收藏夹
    @Insert("insert ignore into category(id,user_id,categoryname,introduction) " +
            "values(null," +
            "(select id from users where users.userName=#{user.username})," +
            "#{category.categoryname}," +
            "#{category.introduction})")
    public int insertCategoryByUser(@Param("user") User user,
                                    @Param("category") ArticleCategory articleCategory);


    //    b.增加一篇文章进一个收藏夹
    @Insert("insert into article(id,user_id,category_id,article_id) " +
            "values(" +
            "null," +
            "#{user.id}," +
            "#{category.id}," +
            "#{article.articleid})")
    public int insertArticleByUserCategory(@Param("user") User user,
                                           @Param("article") Article article,
                                           @Param("category") ArticleCategory articleCategory);


    //查询用户的id
    @Select("select id from users where userName=#{username}")
    public int selectUserIdByName(User user);


    @Select("select category.id " +
            "from category,users " +
            "where category.categoryname=#{category.categoryname} " +
            "and category.user_id=#{user.id} " +
            "and users.id=category.user_id")
    public int test(@Param("category") ArticleCategory articleCategory,
                    @Param("user") User user);

    /*
     * 删除
     */
//    a.删除单独指定的文章
    @Delete("delete article " +
            "from article,users,category " +
            "where users.userName=#{user.username} " +
            "and category.categoryname=#{category.categoryname} " +
            "and article.article_id=#{article.articleid} " +
            "and category.id=article.category_id " +
            "and users.id=article.user_id")
    public int deleteByArticle(@Param("user") User user,
                               @Param("category") ArticleCategory articleCategory,
                               @Param("article") Article article);

    //    b.删除收藏夹下的所有文章
    @Delete("delete article " +
            "from users,article,category " +
            "where users.userName=#{user.username} " +
            "and category.categoryname=#{category.categoryname} " +
            "and category.id=article.category_id " +
            "and users.id=article.user_id")
    public int deleteByUserCategory(@Param("user") User user,
                                    @Param("category") ArticleCategory articleCategory);

    //    c.删除收藏夹
    @Delete("delete category " +
            "from users,category " +
            "where users.userName=#{user.username} " +
            "and category.categoryname=#{category.categoryname} " +
            "and users.id=category.user_id")
    public int deleteAllByUserCategory(@Param("user") User user,
                                       @Param("category") ArticleCategory articleCategory);


    /*
     * 修改
     */
//    a.修改文件夹名称
    @Update("update category,users " +
            "set categoryname=#{new} " +
            "where categoryname=#{category.categoryname} " +
            "and users.userName=#{user.username} " +
            "and users.id=category.user_id ")
    public int updateCategoryName(@Param("user") User user,
                                  @Param("category") ArticleCategory articleCategory,
                                  @Param("new") String categoryname);

    /*
     * 查询
     */
//    a.查出一个人的所有文件夹名称
    @Select("select * " +
            "from users,category" +
            " where username=#{username} " +
            "and users.id=category.user_id")
    public List<ArticleCategory> selectCategoryByUser(User user);


    //    b.查出指定用户，给定文件夹位置，下的所有文件
//    @Select("select article.id,article.user_id,article.article_id," +
//            "from article,category,users " +
//            "where categoryname=#{category.categoryname} " +
//            "and article.category_id=category.id "+
//            "and article.user_id=users.id " +
//            "and userName=#{user.username} "
//            )
    @Select("select article.id,article.user_id,article.category_id,article.article_id " +
            "from article " +
            " join users on article.user_id=users.id " +
            " join category on article.category_id=category.id " +
            "where categoryname=#{category.categoryname} and userName=#{user.username} ")
    public List<Article> selectArticleByUserCategory(@Param("user") User user,
                                                     @Param("category") ArticleCategory articleCategory);

    //通过用户姓名和文章的id得到text
    @Select("select text.id,text.title, text.phone, text.subtitle " +
            "from text " +
            "join article on article.article_id=text.id " +
            "join users on article.user_id=users.id " +
            "where article_id=#{article.articleid} " +
            "and userName=#{user.username}")
    public Text selectTextsByArticle(@Param("user") User user,
                                     @Param("article") Article article);


    @Select("select * " +
            "from article " +
            "join text on article.article_id=text.id " +
            "join users on article.user_id=users.id " +
            "where users.userName=#{user.username} " +
            "and text.title=#{text.title} ")
    public Article selectArticleByTest(@Param("user") User user, @Param("text") Text text);


    //获取text的id值
    @Select("select text.id from text where title=#{title}")
    public int selectArticleIdByTest(Text text);

}
